<%@Page Language="VB"%>

<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDb" %>

<%@ Register TagPrefix="wrox" TagName="connect" Src="..\global\connect-strings.ascx" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html><head>
<title>Editing Data in a DataGrid Control</title>
<style type="text/css">
body, td {font-family:Tahoma,Arial,sans-serif; font-size:10pt}
input {font-family:Tahoma,Arial,sans-serif; font-size:9pt}
.heading {font-family:Tahoma,Arial,sans-serif; font-size:14pt; font-weight:bold}
.subhead {font-family:Tahoma,Arial,sans-serif; font-size:12pt; font-weight:bold; padding-bottom:5px}
.cite {font-family:Tahoma,Arial,sans-serif; font-size:8pt}

.rHead {font-family:Lucida Handwriting,Comic Sans MS,Tahoma,Arial;
        font-size:14pt; font-weight:bold; padding:8px; color:green}
.rItem {font-family:Lucida Handwriting,Comic Sans MS,Tahoma,Arial,sans-serif;
        font-size:10pt}
.rFoot {font-family:Tahoma,Arial; font-size:8pt; padding:8px; color:darkgray}

</style></head>
<body bgcolor="#ffffff">
<span class="heading">Editing Data in a DataGrid Control</span><hr />
<!--------------------------------------------------------------------------->

<%'-- insert connection string script --%>
<wrox:connect id="ctlConnectStrings" runat="server" />

<div id="outError" runat="server" />

<ASP:Label id="lblSQL" runat="server" /><p />

<form runat="server">

  <ASP:DataGrid id="MyDataGrid" runat="server"
       CellPadding = "2"
       EditItemStyle-BackColor="yellow"
       DataKeyField="ISBN"
       OnEditCommand="DoItemEdit"
       OnUpdateCommand="DoItemUpdate"
       OnCancelCommand="DoItemCancel"
       AutoGenerateColumns="False">

    <Columns>

      <ASP:BoundColumn DataField="ISBN" HeaderText="ISBN" ReadOnly="True" />

      <ASP:TemplateColumn HeaderText="Title">
        <ItemTemplate>
          <ASP:Label Text='<%# Container.DataItem("Title") %>' runat="server" />
        </ItemTemplate>
        <EditItemTemplate>
          <ASP:TextBox id="txtTitle" Size="60"
               Text='<%# Container.DataItem("Title") %>' runat="server" />
        </EditItemTemplate>
      </ASP:TemplateColumn>

      <ASP:BoundColumn DataField="PublicationDate" HeaderText="Published" />

      <ASP:EditCommandColumn
           EditText="Edit"
           CancelText="Cancel"
           UpdateText="Update" />

    </Columns>

  </ASP:DataGrid>

</form>

<!--------------------------------------------------------------------------->

<script language="vb" runat="server">

Sub Page_Load()
   If Not Page.IsPostback Then
      BindDataGrid() 'create data set and bind to grid control
   End If
End Sub


Sub DoItemEdit(objSource As Object, objArgs As DataGridCommandEventArgs)

   lblSQL.Text = ""   'clear text from label that shows SQL statement

   'set the EditItemIndex property of the grid to this item's index
   MyDataGrid.EditItemIndex = objArgs.Item.ItemIndex
   BindDataGrid()  'bind the data and display it

End Sub


Sub DoItemUpdate(objSource As Object, objArgs As DataGridCommandEventArgs)

   'get a reference to the title and publication date text boxes
   Dim objTitleCtrl, objPubDateCtrl As TextBox
   objTitleCtrl = CType(objArgs.Item.FindControl("txtTitle"), TextBox)
   objPubDateCtrl = objArgs.Item.Cells(2).Controls(0)

   'create a suitable SQL statement and execute it
   Dim strSQL As String
   strSQL = "UPDATE Booklist SET Title='" & objTitleCtrl.Text & "', " _
          & "PublicationDate='" & objPubDateCtrl.Text & "' " _
          & "WHERE ISBN='" & MyDataGrid.DataKeys(objArgs.Item.ItemIndex) & "'"
   ExecuteSQLStatement(strSQL)

   'set EditItemIndex property of grid to -1 to switch out of Edit mode
   MyDataGrid.EditItemIndex = -1
   BindDataGrid()  'bind the data and display it

End Sub


Sub DoItemCancel(objSource As Object, objArgs As DataGridCommandEventArgs)

   'set EditItemIndex property of grid to -1 to switch out of Edit mode
   MyDataGrid.EditItemIndex = -1
   BindDataGrid()  'bind the data and display it

End Sub


Sub ExecuteSQLStatement(strSQL)

   'this is where the SQL statement would be executed against the
   'original data source. In this example, we're simply displaying
   'the statement in a Label on the page
   lblSQL.Text = "<b>The SQL statement that would be executed is:</b><br />" & strSQL

End Sub


Sub BindDataGrid()

   'get connection string from ..\global\connect-strings.ascx user control
   Dim strConnect As String  = ctlConnectStrings.OLEDBConnectionString

   'create a SQL statement to select some rows from the database
   Dim strSelect As String
   strSelect = "SELECT * FROM BookList WHERE ISBN LIKE '18610025%'"

   'create a variable to hold an instance of a DataReader object
   Dim objDataReader As OleDbDataReader

   Try

      'create a new Connection object using the connection string
      Dim objConnect As New OleDbConnection(strConnect)

      'open the connection to the database
      objConnect.Open()

      'create a new Command using the connection object and select statement
      Dim objCommand As New OleDbCommand(strSelect, objConnect)

      'execute the SQL statement against the command to get the DataReader
      objDataReader = objCommand.ExecuteReader()

    Catch objError As Exception

       'display error details
       outError.InnerHtml = "<b>* Error while accessing data</b>.<br />" _
           & objError.Message & "<br />" & objError.Source & "<p />"
       Exit Sub  ' and stop execution

    End Try

   'set the DataSource property and bind the grid
   MyDataGrid.DataSource = objDataReader
   MyDataGrid.DataBind()

End Sub

</script>

<!--------------------------------------------------------------------------->
<hr /><span class="cite">[<a href="../global/viewsource.aspx">view source</a>] &nbsp; &nbsp; &nbsp; &nbsp;
&copy;2001 <a class="cite" href="http://www.wrox.com/">Wrox Press</a> -
<a class="cite" href="http://www.wrox.com/Books/Book_Details.asp?isbn=1861004885">Professional ASP.NET</a> (ISBN: 1-861004-88-5)</span>
</body>
</html>
